package top.abilities.util.utils.Excel;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * @Auhtor: ciro
 * @Description
 * @Date: 2018/1/5 17:16
 * @Modified By:
 */
    public class Excel2Util<T> {
        public CellStyle getCellStyle(Workbook workbook, boolean isHeader){
            CellStyle style = workbook.createCellStyle();
            style.setBorderBottom(CellStyle.BORDER_THIN);
            style.setBorderLeft(CellStyle.BORDER_THIN);
            style.setBorderRight(CellStyle.BORDER_THIN);
            style.setBorderTop(CellStyle.BORDER_THIN);
            style.setLocked(true);
            if (isHeader) {
                style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);
                // style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
                style.setFillPattern(CellStyle.SOLID_FOREGROUND);
                Font font = workbook.createFont();
                font.setColor(IndexedColors.BLACK.index);
                // font.setColor(HSSFColor.BLACK.index);
                font.setFontHeightInPoints((short) 12);
                font.setBoldweight(Font.BOLDWEIGHT_BOLD);
                style.setFont(font);
            }
            return style;
        }

        public void generateHeader(Workbook workbook, Sheet sheet, String[] headerColumns){
            CellStyle style = getCellStyle(workbook, true);
            Row row = sheet.createRow(0);
            row.setHeightInPoints(30);
            for(int i=0;i<headerColumns.length;i++){
                Cell cell = row.createCell(i);
                String[] column = headerColumns[i].split("_#_");
                sheet.setColumnWidth(i, Integer.valueOf(column[1]));
                cell.setCellValue(column[0]);
                cell.setCellStyle(style);
            }
        }

        public static InputStream writeExcelToStream(Workbook workbook, Sheet sheet) {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            try {
                workbook.write(bos);
            } catch (Exception e) {
                e.printStackTrace();
            }

            ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
            return bis;
        }
        public static OutputStream writeExcelToOutStream(Workbook workbook, Sheet sheet){
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            try {
                workbook.write(bos);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return bos;
        }
        @SuppressWarnings({ "rawtypes", "unchecked" })
        public Sheet creatAuditSheet(Workbook workbook, String sheetName,
                                     List<T> dataset, String[] headerColumns, String[] fieldColumns) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {

            Sheet sheet = workbook.createSheet(sheetName);
            //sheet.protectSheet(""); //保护生成Excel文档，设置密码访问.
            //自动对生成的Excel 文档第一行标题栏设置成filter 过滤形式, 方便用户使用
            char[] endChar = Character.toChars( 'A' + (headerColumns.length - 1) );
            String rangeAddress = "A1:" + String.valueOf(endChar) + "1";
            sheet.setAutoFilter(CellRangeAddress.valueOf(rangeAddress));

            generateHeader(workbook,sheet,headerColumns);
            CellStyle style = getCellStyle(workbook,false);
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            int rowNum = 0;
            for(T t:dataset){
                rowNum++ ;
                Row row = sheet.createRow(rowNum);
                row.setHeightInPoints(25);
                for(int i = 0; i < fieldColumns.length; i++){
                    String fieldName = fieldColumns[i] ;

                    String getMethodName = "get" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
                    try {
                        Class clazz = t.getClass();
                        Method getMethod;
                        getMethod = clazz.getMethod(getMethodName, new Class[]{} );
                        Object value = getMethod.invoke(t, new Object[]{});
                        String cellValue = "";
                        if (value instanceof Date){
                            Date date = (Date)value;
                            cellValue = sd.format(date);
                        }else{
                            cellValue = null != value ? value.toString() : "";
                        }
                        Cell cell = row.createCell(i);
                        cell.setCellStyle(style);
                        cell.setCellValue(cellValue);

                    } catch (Exception e) {

                    }
                }
            }
            return sheet;
        }

        /**
         *  删除空行(以及带格式的空行)
         * @param sheet
         * @return
         */
        public static Sheet clearSheetRowNull(Sheet sheet){
            try {
                if (sheet ==null)return sheet;
                CellReference cellReference = new CellReference("A4");
                boolean flag = false;
                System.out.println("总行数："+(sheet.getLastRowNum()+1));
                for (int i = cellReference.getRow(); i <= sheet.getLastRowNum();) {
                    Row r = sheet.getRow(i);
                    if(r == null){
                        // 如果是空行（即没有任何数据、格式），直接把它以下的数据往上移动
                        sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
                        continue;
                    }
                    flag = false;
                    for(Cell c:r){
                        if(c.getCellType() != Cell.CELL_TYPE_BLANK){
                            flag = true;
                            break;
                        }
                    }
                    if(flag){
                        i++;
                        continue;
                    }
                    else{//如果是空白行（即可能没有数据，但是有一定格式）
                        if(i == sheet.getLastRowNum())//如果到了最后一行，直接将那一行remove掉
                            sheet.removeRow(r);
                        else//如果还没到最后一行，则数据往上移一行
                            sheet.shiftRows(i+1, sheet.getLastRowNum(),-1);
                    }
                }
                System.out.println("总行数："+(sheet.getLastRowNum()+1));
            }catch (Exception e){

            }
            return sheet;
        }

        public static void main(String[] args) {
            try {
                Workbook workbook = WorkbookFactory.create(new File("C:\\Users\\MEV\\Desktop\\TestRow.xlsx"));
                clearSheetRowNull(workbook.getSheetAt(0));
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }
        }

    }

